﻿/*---------------------------------------------------------------------------------
-----------------***DaLianQianYi***-----------------------
-----------------作者：大连仟亿科技------------------------------------------------------
-----------------联系作者：www.qianyikeji.com----------------------------------
-----------------时间：2018/8/17 17:44:38------------------------------------------
----------------------------------------------------------------------------------*/


using Donal.Xms.Common;
using Donal.Xms.DBUtility;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;

namespace Donal.Xms.DAL
{
    /// <summary>
    /// 数据访问类
    /// </summary>
    public partial class coupons_allotment
    {
        private string databaseprefix; //数据库表名前缀
        public coupons_allotment(string _databaseprefix)
        {
            databaseprefix = _databaseprefix;
        }

        #region 基本方法================================
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from  " + databaseprefix + "coupons_allotment");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.coupons_allotment model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();//数据字段
            StringBuilder str2 = new StringBuilder();//数据参数
                                                     //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("insert into " + databaseprefix + "coupons_allotment(");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + ",");//拼接字段
                        str2.Append("@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") values (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY;");
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), paras.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(SqlConnection conn, SqlTransaction trans, Model.coupons_allotment model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();//数据字段
            StringBuilder str2 = new StringBuilder();//数据参数
                                                     //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("insert into " + databaseprefix + "coupons_allotment(");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + ",");//拼接字段
                        str2.Append("@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") values (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY;");
            object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), paras.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.coupons_allotment model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("update  " + databaseprefix + "coupons_allotment set ");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + "=@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(" where id=@id ");
            paras.Add(new SqlParameter("@id", model.id));
            return DbHelperSQL.ExecuteSql(strSql.ToString(), paras.ToArray()) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from  " + databaseprefix + "coupons_allotment ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.coupons_allotment GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.coupons_allotment model = new Model.coupons_allotment();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                str1.Append(p.Name + ",");//拼接字段
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from " + databaseprefix + "coupons_allotment");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;
            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public int GetCount(int coupon_id, int user_id)
        {
            return (int)DbHelperSQL.GetSingle("select count(1) from " + databaseprefix + "coupons_allotment where coupon_id = " + coupon_id + " and user_id=" + user_id);
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM " + databaseprefix + "coupons_allotment");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public List<Model.coupons_allotment> GetList(SqlConnection conn, SqlTransaction trans, int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM " + databaseprefix + "coupons_allotment");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString());

            if (Utils.CheckDataSet(ds))
            {
                List<Model.coupons_allotment> coupons_Allotments = new List<Model.coupons_allotment>();

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    coupons_Allotments.Add(DataRowToModel(dr));
                }

                return coupons_Allotments;
            }
            else
            {
                return null;
            }

        }

        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM " + databaseprefix + "coupons_allotment");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        #endregion

        #region 扩展方法================================
        /// <summary>
        /// 返回指定列
        /// </summary>
        public string GetValue(int id, string fileName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 " + fileName + " from " + databaseprefix + "coupons_allotment");
            strSql.Append(" where id=" + id);
            string title = Convert.ToString(DbHelperSQL.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(title))
            {
                return "";
            }
            return title;
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "coupons_allotment set " + strValue);
            strSql.Append(" where id=" + id);
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }


        /// <summary>
        /// 用户充值获取活动
        /// </summary>
        public bool reachAddCouponsLog(string order_no, decimal orderAmount, string erpCode)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        Model.company company = new company(databaseprefix).GetModel(conn, trans, erpCode);
                        if (erpCode == null)
                        {
                            trans.Rollback();
                            conn.Close();

                            Utils.WriteLogs("用户充值获取活动错误", "没有找到相关会员信息 erpCode=" + erpCode, DTKeys.NOERPCODE, DTKeys.EXCEPION_PATH, order_no);
                            return true;
                        }

                        //查找启用状态下的充值活动（category=0 充值 or category=1 订单 or category=2 全部)
                        List<Model.actions> actions = new actions(databaseprefix).GetModelList(conn, trans, " conditions_amount <= " + orderAmount + " and  (category=0 or category=2) and status=1 and action_type=1  and ((DATEDIFF(DAY,getdate(),start_time)<=0 and DATEDIFF(DAY,getdate(),end_time)>=0) or expiration_form !=0) ");

                        if (actions != null && actions.Count > 0)
                        {
                            List<Model.actions> newActions = new List<Model.actions>();

                            List<Model.actions> temActions = actions.FindAll(a => a.conditions_amount == orderAmount);
                            if (temActions != null && temActions.Count > 0)
                            {
                                newActions = temActions;
                            }
                            else
                            {
                                newActions.Add(actions[0]);
                            }
                            Dictionary<int, int> dic_coupon_quantity = new Dictionary<int, int>();
                            coupons coupDal = new coupons(databaseprefix);
                            foreach (Model.actions a in newActions)
                            {
                                string ids = "";
                                if (a.coupons.Count > 0)
                                {
                                    foreach (Model.action_coupons ac in a.coupons)
                                    {
                                        if (!dic_coupon_quantity.ContainsKey(ac.coupon_id))
                                        {
                                            dic_coupon_quantity.Add(ac.coupon_id, ac.quantity);
                                        }
                                        else
                                        {
                                            dic_coupon_quantity[ac.coupon_id] += ac.quantity;
                                        }
                                        ids += ac.coupon_id + ",";
                                    }
                                }

                                if (ids != "")
                                {
                                    ids = ids.Substring(0, ids.Length - 1);
                                }

                                List<Model.coupons> coupons = coupDal.GetModelList(conn, trans, ids);

                                if (coupons != null && coupons.Count > 0)
                                {
                                    foreach (Model.coupons c in coupons)
                                    {
                                        string overTime = "";
                                        switch (c.expiration_form)
                                        {
                                            case 0:
                                                overTime = c.end_time.ToString();
                                                break;
                                            case 1:
                                                overTime = DateTime.Now.AddDays(c.expiration_num).ToString();
                                                break;
                                            case 2:
                                                overTime = "";
                                                break;
                                        }

                                        //添加分配表                                           
                                        for (int i = 0; i < dic_coupon_quantity[c.id]; i++)
                                        {
                                            Add(conn, trans, new Model.coupons_allotment()
                                            {
                                                add_time = c.add_time,
                                                allotment_time = DateTime.Now,
                                                amount = c.amount,
                                                conditions_amount = c.conditions_amount,
                                                contents = c.contents,
                                                coupon_id = c.id,
                                                coupon_ids = c.coupon_ids,
                                                end_time = c.end_time,
                                                expiration_form = c.expiration_form,
                                                expiration_num = c.expiration_num,
                                                is_lock = 0,
                                                order_no = "",
                                                over_time = overTime,
                                                sort_id = c.sort_id,
                                                start_time = c.start_time,
                                                status = c.status,
                                                superposition = c.superposition,
                                                title = c.title,
                                                user_id = company.id,
                                                backcolor = c.backcolor,
                                                img_url = c.img_url
                                            }, 3, "", "", "用户充值获取优惠券");
                                        }
                                    }
                                }

                            }
                        }

                        trans.Commit();
                        conn.Close();
                        return true;

                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        conn.Close();

                        Utils.WriteLogs("更新与添加优惠券信息时异常", e.Message + " " + e.StackTrace, erpCode, DTKeys.EXCEPION_PATH, order_no);
                        return false;
                    }
                }
            }

        }


        /// <summary>
        /// 添加优惠券使用记录
        /// </summary>
        public bool AddLog(bool isUpdate, string strValue, string strWhere, string companyName, string erpCode, decimal orderAmount, int user_Id)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        if (isUpdate)
                        {
                            StringBuilder strSql = new StringBuilder();
                            strSql.Append("update " + databaseprefix + "coupons_allotment set " + strValue);
                            strSql.Append(" where " + strWhere);

                            if (DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString()) <= 0)
                            {
                                trans.Rollback();
                                conn.Close();

                                Utils.WriteLogs("更新优惠券时出错", strValue, erpCode, DTKeys.EXCEPION_PATH);
                                return false;
                            }

                            //获取订单所使用的优惠券
                            List<Model.coupons_allotment> coupons_Allotments = GetList(conn, trans, 0, strWhere, "id");

                            if (coupons_Allotments != null && coupons_Allotments.Count > 0)
                            {
                                foreach (Model.coupons_allotment ca in coupons_Allotments)
                                {
                                    //添加优惠券使用记录
                                    new coupons_used_logs(databaseprefix).Add(conn, trans, new Model.coupons_used_logs()
                                    {
                                        add_time = ca.add_time,
                                        allotment_time = ca.allotment_time,
                                        amount = ca.amount,
                                        company = companyName,
                                        conditions_amount = ca.conditions_amount,
                                        contents = ca.contents,
                                        coupon_id = ca.coupon_id,
                                        coupon_ids = ca.coupon_ids,
                                        end_time = ca.end_time,
                                        expiration_form = ca.expiration_form,
                                        expiration_num = ca.expiration_num,
                                        goods_category_ids = ca.goods_category_ids,
                                        is_lock = ca.is_lock,
                                        log_status = 0,
                                        log_time = DateTime.Now,
                                        order_no = ca.order_no,
                                        over_time = ca.over_time,
                                        remark = "",
                                        sort_id = ca.sort_id,
                                        start_time = ca.start_time,
                                        status = ca.status,
                                        superposition = ca.superposition,
                                        title = ca.title,
                                        used_time = DateTime.Now,
                                        user_erp_code = erpCode,
                                        user_id = ca.user_id,
                                        backcolor = ca.backcolor,
                                        img_url = ca.img_url,
                                        coupons_allotment_id = ca.id
                                    });
                                }
                            }
                        }


                        //查找活动
                        //查找启用状态下的订单活动（category=0 充值 or category=1 订单 or category=2 全部)
                        List<Model.actions> actions = new actions(databaseprefix).GetModelList(conn, trans, " conditions_amount <= " + orderAmount + " and (category=1 or category=2) and status=1 and action_type=1 and ((DATEDIFF(DAY,getdate(),start_time)<=0 and DATEDIFF(DAY,getdate(),end_time)>=0) or expiration_form !=0) ");

                        if (actions != null && actions.Count > 0)
                        {
                            List<Model.actions> newActions = new List<Model.actions>();

                            List<Model.actions> temActions = actions.FindAll(a => a.conditions_amount == orderAmount);//查找 订单金额等于活动金额的活动
                            if (temActions != null && temActions.Count > 0)
                            {
                                newActions = temActions;
                            }
                            else
                            {
                                newActions.Add(actions[0]);//默认活动列表的第一个活动，满足活动标准的最高级别活动。
                            }

                            Dictionary<int, int> dic_coupon_quantity = new Dictionary<int, int>();

                            coupons coupDal = new coupons(databaseprefix);
                            foreach (Model.actions a in newActions)
                            {
                                string ids = "";
                                if (a.coupons.Count > 0)
                                {
                                    foreach (Model.action_coupons ac in a.coupons)
                                    {
                                        if (!dic_coupon_quantity.ContainsKey(ac.coupon_id))
                                        {
                                            dic_coupon_quantity.Add(ac.coupon_id, ac.quantity);
                                        }
                                        else
                                        {
                                            dic_coupon_quantity[ac.coupon_id] += ac.quantity;
                                        }
                                        ids += ac.coupon_id + ",";
                                    }
                                }

                                if (ids != "")
                                {
                                    ids = ids.Substring(0, ids.Length - 1);
                                }

                                List<Model.coupons> coupons = coupDal.GetModelList(conn, trans, ids);

                                if (coupons != null && coupons.Count > 0)
                                {
                                    foreach (Model.coupons c in coupons)
                                    {
                                        string overTime = "";
                                        switch (c.expiration_form)
                                        {
                                            case 0:
                                                overTime = c.end_time.ToString();
                                                break;
                                            case 1:
                                                overTime = DateTime.Now.AddDays(c.expiration_num).ToString();
                                                break;
                                            case 2:
                                                overTime = "";
                                                break;
                                        }

                                        //分配优惠券
                                        for (int i = 0; i < dic_coupon_quantity[c.id]; i++)
                                        {
                                            Add(conn, trans, new Model.coupons_allotment()
                                            {
                                                add_time = c.add_time,
                                                allotment_time = DateTime.Now,
                                                amount = c.amount,
                                                conditions_amount = c.conditions_amount,
                                                contents = c.contents,
                                                coupon_id = c.id,
                                                coupon_ids = c.coupon_ids,
                                                end_time = c.end_time,
                                                expiration_form = c.expiration_form,
                                                expiration_num = c.expiration_num,
                                                is_lock = 0,
                                                order_no = "",
                                                over_time = overTime,
                                                sort_id = c.sort_id,
                                                start_time = c.start_time,
                                                status = c.status,
                                                superposition = c.superposition,
                                                title = c.title,
                                                user_id = user_Id,
                                                backcolor = c.backcolor,
                                                img_url = c.img_url
                                            }, 2, erpCode, companyName, "订单满获取");
                                        }
                                    }
                                }

                            }
                        }

                        trans.Commit();
                        conn.Close();
                        return true;

                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        conn.Close();

                        Utils.WriteLogs("更新与添加优惠券信息时异常", e.Message + " " + e.StackTrace, erpCode, DTKeys.EXCEPION_PATH);
                        return false;
                    }
                }
            }

        }


        /// <summary>
        /// 更新优惠券状态
        /// </summary>
        public bool CouponsLog(string strValue, string strWhere, string companyName, string erpCode, int order_status, string remarks)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("update " + databaseprefix + "coupons_allotment set " + strValue);
                        strSql.Append(" where " + strWhere);

                        if (DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString()) <= 0)
                        {
                            trans.Rollback();
                            conn.Close();

                            Utils.WriteLogs("更新优惠券时出错", strValue, erpCode, DTKeys.EXCEPION_PATH);
                            return false;
                        }

                        int log_statu = 0;

                        switch (order_status)
                        {
                            case 1:
                            case 2:
                            case 3:
                                log_statu = 5;
                                break;
                            case 8:
                            case 9:
                                log_statu = 6;
                                break;
                            case 4:
                            case 5:
                            case 6:
                            case 7:
                                log_statu = 0;
                                break;
                        }

                        List<Model.coupons_allotment> coupons_Allotments = GetList(conn, trans, 0, strWhere, "id");

                        if (coupons_Allotments != null && coupons_Allotments.Count > 0)
                        {
                            foreach (Model.coupons_allotment ca in coupons_Allotments)
                            {
                                if (new coupons_used_logs(databaseprefix).Add(conn, trans, new Model.coupons_used_logs()
                                {
                                    add_time = ca.add_time,
                                    allotment_time = ca.allotment_time,
                                    amount = ca.amount,
                                    company = companyName,
                                    conditions_amount = ca.conditions_amount,
                                    contents = ca.contents,
                                    coupon_id = ca.coupon_id,
                                    coupon_ids = ca.coupon_ids,
                                    end_time = ca.end_time,
                                    expiration_form = ca.expiration_form,
                                    expiration_num = ca.expiration_num,
                                    goods_category_ids = ca.goods_category_ids,
                                    is_lock = ca.is_lock,
                                    log_status = log_statu,
                                    log_time = DateTime.Now,
                                    order_no = ca.order_no,
                                    over_time = ca.over_time,
                                    remark = remarks,
                                    sort_id = ca.sort_id,
                                    start_time = ca.start_time,
                                    status = ca.status,
                                    superposition = ca.superposition,
                                    title = ca.title,
                                    used_time = DateTime.Now,
                                    user_erp_code = erpCode,
                                    user_id = ca.user_id,
                                    backcolor = ca.backcolor,
                                    img_url = ca.img_url,
                                    coupons_allotment_id = ca.id

                                }) < 0)
                                {
                                    trans.Rollback();
                                    conn.Close();

                                    Utils.WriteLogs("更新优惠券使用记录时错误", "优惠券：" + ca.contents, erpCode, DTKeys.EXCEPION_PATH);
                                    return false;
                                }
                            }
                        }
                        else
                        {
                            trans.Rollback();
                            conn.Close();

                            Utils.WriteLogs("更新优惠券信息时错误", "没有找到相关优惠券", erpCode, DTKeys.EXCEPION_PATH);
                            return false;
                        }


                        trans.Commit();
                        conn.Close();
                        return true;

                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        conn.Close();

                        Utils.WriteLogs("更新与添加优惠券信息时异常", e.Message + " " + e.StackTrace, erpCode, DTKeys.EXCEPION_PATH);
                        return false;
                    }
                }
            }

        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(string strValue, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "coupons_allotment set " + strValue);
            strSql.Append(" where " + strWhere);
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(SqlConnection conn, SqlTransaction trans, string strValue, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "coupons_allotment set " + strValue);
            strSql.Append(" where " + strWhere);
            return DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString()) > 0;
        }

        /// <summary>
        /// 将对象转换实体
        /// </summary>
        public Model.coupons_allotment DataRowToModel(DataRow row)
        {
            Model.coupons_allotment model = new Model.coupons_allotment();
            if (row != null)
            {
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    //查找实体是否存在列表相同的公共属性
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        proInfo.SetValue(model, row[i], null);//用索引值设置属性值
                    }
                }
            }
            return model;
        }

        /// <summary>
        /// 整组分配优惠券
        /// </summary>
        /// <param name="group_id"></param>
        /// <param name="coupons_ids"></param>
        /// <returns></returns>
        public bool allotment(int group_id, Dictionary<int, int> dic_coupons_quantity, Model.manager manager, string ip, string _action_type)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        DataSet userIds = DbHelperSQL.Query(conn, trans, "select id,erpCode,company from dt_company where status = 0 and group_id=" + group_id);

                        if (!Utils.CheckDataSet(userIds))
                        {
                            trans.Rollback();
                            conn.Close();

                            Utils.WriteLogs("整组分配优惠券失败", "没有找到相关会员信息", DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                            return false;
                        }

                        string over_time = "";

                        Model.coupons_allotment coupons_Allotment;
                        Model.coupons coupons = new Model.coupons();

                        if (dic_coupons_quantity != null && dic_coupons_quantity.Count > 0)
                        {
                            foreach (KeyValuePair<int, int> kvp in dic_coupons_quantity)
                            {
                                coupons = new coupons(databaseprefix).GetModel(conn, trans, kvp.Key);
                                switch (coupons.expiration_form)
                                {
                                    case 0://自动过期
                                        over_time = coupons.end_time.ToString();
                                        break;
                                    case 1://指定天数过期
                                        over_time = DateTime.Now.AddDays(coupons.expiration_num).ToString();
                                        break;
                                    case 2://永不过期
                                        over_time = "";
                                        break;
                                }

                                foreach (DataRow udr in userIds.Tables[0].Rows)
                                {
                                    for (int i = 0; i < kvp.Value; i++)
                                    {
                                        coupons_Allotment = new Model.coupons_allotment
                                        {
                                            add_time = coupons.add_time,
                                            allotment_time = DateTime.Now,
                                            amount = coupons.amount,
                                            conditions_amount = coupons.conditions_amount,
                                            contents = coupons.contents,
                                            coupon_ids = coupons.coupon_ids,
                                            end_time = coupons.end_time,
                                            expiration_form = coupons.expiration_form,
                                            expiration_num = coupons.expiration_num,
                                            coupon_id = coupons.id,
                                            is_lock = 0,
                                            order_no = "",
                                            over_time = over_time,
                                            sort_id = coupons.sort_id,
                                            start_time = coupons.start_time,
                                            status = coupons.status,
                                            superposition = coupons.superposition,
                                            title = coupons.title,
                                            user_id = int.Parse(udr[0].ToString()),
                                            backcolor = coupons.backcolor,
                                            img_url = coupons.img_url
                                        };

                                        if (Add(conn, trans, coupons_Allotment, 4, udr["erpCode"].ToString(), udr["company"].ToString(), "整组分配优惠券", manager, ip, _action_type) <= 0)
                                        {
                                            trans.Rollback();
                                            conn.Close();

                                            Utils.WriteLogs("整组分配优惠券失败", "添加到分配表时出错", DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                                            return false;
                                        }
                                    }
                                }
                            }

                            trans.Commit();
                            conn.Close();
                            return true;
                        }
                        else
                        {
                            trans.Rollback();
                            conn.Close();

                            Utils.WriteLogs("整组分配优惠券失败", "没有找到相关优惠券信息", DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                            return false;
                        }

                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        conn.Close();

                        Utils.WriteLogs("整组分配优惠券异常", e.Message + " " + e.StackTrace, DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                        return false;
                    }
                }
            }

        }

        /// <summary>
        /// 指定会员分配优惠券
        /// </summary>
        /// <param name="user_ids"></param>
        /// <param name="coupons_ids"></param>
        /// <returns></returns>
        public bool allotmentByErpCodes(string erpCodes, Dictionary<int, int> dic_coupons_quantity, Model.manager manager, string ip, string _action_type)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string[] uids = erpCodes.Split(',');

                        if (uids.Length <= 0)
                        {
                            trans.Rollback();
                            conn.Close();

                            Utils.WriteLogs("指定会员分配优惠券失败", "没有找到相关会员信息", DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                            return false;
                        }


                        DataSet buddyDs = new company(databaseprefix).GetList(0, "erpCode in(" + erpCodes + ")", "id");

                        if (!Utils.CheckDataSet(buddyDs))
                        {
                            Utils.WriteLogs("指定会员分配优惠券失败", "没有找到相关会员信息", DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                            return false;
                        }


                        string over_time = "";

                        Model.coupons coupons = new Model.coupons();
                        Model.coupons_allotment coupons_Allotment;

                        if (dic_coupons_quantity != null && dic_coupons_quantity.Count > 0)
                        {
                            foreach (KeyValuePair<int, int> kvp in dic_coupons_quantity)
                            {
                                coupons = new coupons(databaseprefix).GetModel(conn, trans, kvp.Key);
                                switch (coupons.expiration_form)
                                {
                                    case 0://自动过期
                                        over_time = coupons.end_time.ToString();
                                        break;
                                    case 1://指定天数过期
                                        over_time = DateTime.Now.AddDays(coupons.expiration_num).ToString();
                                        break;
                                    case 2://永不过期
                                        over_time = "";
                                        break;
                                }

                                foreach (DataRow dr in buddyDs.Tables[0].Rows)
                                {

                                    for (int i = 0; i < kvp.Value; i++)
                                    {
                                        coupons_Allotment = new Model.coupons_allotment
                                        {
                                            add_time = coupons.add_time,
                                            allotment_time = DateTime.Now,
                                            amount = coupons.amount,
                                            conditions_amount = coupons.conditions_amount,
                                            contents = coupons.contents,
                                            coupon_ids = coupons.coupon_ids,
                                            end_time = coupons.end_time,
                                            expiration_form = coupons.expiration_form,
                                            expiration_num = coupons.expiration_num,
                                            coupon_id = coupons.id,
                                            is_lock = 0,
                                            order_no = "",
                                            over_time = over_time,
                                            sort_id = coupons.sort_id,
                                            start_time = coupons.start_time,
                                            status = coupons.status,
                                            superposition = coupons.superposition,
                                            title = coupons.title,
                                            user_id = int.Parse(dr["id"].ToString()),
                                            backcolor = coupons.backcolor,
                                            img_url = coupons.img_url
                                        };

                                        if (Add(conn, trans, coupons_Allotment, 4, dr["erpCode"].ToString(), dr["company"].ToString(), "指定会员分配优惠券", manager, ip, _action_type) <= 0)
                                        {
                                            trans.Rollback();
                                            conn.Close();

                                            Utils.WriteLogs("指定会员分配优惠券失败", "添加到分配表时出错", DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                                            return false;
                                        }
                                    }

                                }
                            }

                            trans.Commit();
                            conn.Close();
                            return true;
                        }
                        else
                        {
                            trans.Rollback();
                            conn.Close();

                            Utils.WriteLogs("指定会员分配优惠券失败", "没有找到相关优惠券信息", DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                            return false;
                        }

                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        conn.Close();

                        Utils.WriteLogs("指定会员分配优惠券异常", e.Message + " " + e.StackTrace, DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                        return false;
                    }
                }
            }

        }


        /// <summary>
        /// 指定会员分配优惠券
        /// </summary>
        /// <param name="user_ids"></param>
        /// <param name="coupons_ids"></param>
        /// <returns></returns>
        public bool allotment(string user_ids, Dictionary<int, int> dic_coupons_quantity, Model.manager manager, string ip, string _action_type)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string[] uids = user_ids.Split(',');

                        if (uids.Length <= 0)
                        {
                            trans.Rollback();
                            conn.Close();

                            Utils.WriteLogs("指定会员分配优惠券失败", "没有找到相关会员信息", DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                            return false;
                        }


                        DataSet buddyDs = new company(databaseprefix).GetList(0, "id in(" + user_ids + ")", "id");

                        if (!Utils.CheckDataSet(buddyDs))
                        {
                            Utils.WriteLogs("指定会员分配优惠券失败", "没有找到相关会员信息", DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                            return false;
                        }


                        string over_time = "";

                        Model.coupons coupons = new Model.coupons();
                        Model.coupons_allotment coupons_Allotment;

                        if (dic_coupons_quantity != null && dic_coupons_quantity.Count > 0)
                        {
                            foreach (KeyValuePair<int, int> kvp in dic_coupons_quantity)
                            {
                                coupons = new coupons(databaseprefix).GetModel(conn, trans, kvp.Key);
                                switch (coupons.expiration_form)
                                {
                                    case 0://自动过期
                                        over_time = coupons.end_time.ToString();
                                        break;
                                    case 1://指定天数过期
                                        over_time = DateTime.Now.AddDays(coupons.expiration_num).ToString();
                                        break;
                                    case 2://永不过期
                                        over_time = "";
                                        break;
                                }

                                foreach (DataRow dr in buddyDs.Tables[0].Rows)
                                {

                                    for (int i = 0; i < kvp.Value; i++)
                                    {
                                        coupons_Allotment = new Model.coupons_allotment
                                        {
                                            add_time = coupons.add_time,
                                            allotment_time = DateTime.Now,
                                            amount = coupons.amount,
                                            conditions_amount = coupons.conditions_amount,
                                            contents = coupons.contents,
                                            coupon_ids = coupons.coupon_ids,
                                            end_time = coupons.end_time,
                                            expiration_form = coupons.expiration_form,
                                            expiration_num = coupons.expiration_num,
                                            coupon_id = coupons.id,
                                            is_lock = 0,
                                            order_no = "",
                                            over_time = over_time,
                                            sort_id = coupons.sort_id,
                                            start_time = coupons.start_time,
                                            status = coupons.status,
                                            superposition = coupons.superposition,
                                            title = coupons.title,
                                            user_id = int.Parse(dr["id"].ToString()),
                                            backcolor = coupons.backcolor,
                                            img_url = coupons.img_url
                                        };

                                        if (Add(conn, trans, coupons_Allotment, 4, dr["erpCode"].ToString(), dr["company"].ToString(), "指定会员分配优惠券", manager, ip, _action_type) <= 0)
                                        {
                                            trans.Rollback();
                                            conn.Close();

                                            Utils.WriteLogs("指定会员分配优惠券失败", "添加到分配表时出错", DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                                            return false;
                                        }
                                    }

                                }
                            }

                            trans.Commit();
                            conn.Close();
                            return true;
                        }
                        else
                        {
                            trans.Rollback();
                            conn.Close();

                            Utils.WriteLogs("指定会员分配优惠券失败", "没有找到相关优惠券信息", DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                            return false;
                        }

                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        conn.Close();

                        Utils.WriteLogs("指定会员分配优惠券异常", e.Message + " " + e.StackTrace, DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH);
                        return false;
                    }
                }
            }

        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(SqlConnection conn, SqlTransaction trans, Model.coupons_allotment model, int logType, string erpCod, string companyName, string remarks)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();//数据字段
            StringBuilder str2 = new StringBuilder();//数据参数
                                                     //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("insert into " + databaseprefix + "coupons_allotment(");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + ",");//拼接字段
                        str2.Append("@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") values (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY;");
            object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), paras.ToArray());

            int _id = 0;
            if (obj != null)
            {
                _id = Convert.ToInt32(obj);
            }

            //优惠券记录表
            new coupons_used_logs(databaseprefix).Add(conn, trans, new Model.coupons_used_logs()
            {
                add_time = model.add_time,
                allotment_time = model.allotment_time,
                amount = model.amount,
                conditions_amount = model.conditions_amount,
                contents = model.contents,
                coupon_id = model.coupon_id,
                coupon_ids = model.coupon_ids,
                end_time = model.end_time,
                expiration_form = model.expiration_form,
                expiration_num = model.expiration_num,
                goods_category_ids = model.goods_category_ids,
                is_lock = model.is_lock,
                log_status = logType,
                log_time = DateTime.Now,
                order_no = "",
                over_time = model.over_time,
                remark = remarks,
                sort_id = model.sort_id,
                start_time = model.start_time,
                status = model.status,
                superposition = model.superposition,
                title = model.title,
                user_id = model.user_id,
                user_erp_code = erpCod,
                company = companyName,
                backcolor = model.backcolor,
                img_url = model.img_url,
                coupons_allotment_id = _id
            });

            return _id;
        }


        #endregion



        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(SqlConnection conn, SqlTransaction trans, Model.coupons_allotment model,
            int logType, string erpCod, string companyName,
            string remarks, Model.manager manager, string ip, string _action_type)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();//数据字段
            StringBuilder str2 = new StringBuilder();//数据参数
                                                     //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("insert into " + databaseprefix + "coupons_allotment(");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + ",");//拼接字段
                        str2.Append("@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") values (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY;");
            object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), paras.ToArray());

            Model.coupons_used_logs cul = new Model.coupons_used_logs()
            {
                add_time = model.add_time,
                allotment_time = model.allotment_time,
                amount = model.amount,
                conditions_amount = model.conditions_amount,
                contents = model.contents,
                coupon_id = model.coupon_id,
                coupon_ids = model.coupon_ids,
                end_time = model.end_time,
                expiration_form = model.expiration_form,
                expiration_num = model.expiration_num,
                goods_category_ids = model.goods_category_ids,
                is_lock = model.is_lock,
                log_status = logType,
                log_time = DateTime.Now,
                order_no = "",
                over_time = model.over_time,
                remark = remarks,
                sort_id = model.sort_id,
                start_time = model.start_time,
                status = model.status,
                superposition = model.superposition,
                title = model.title,
                user_id = model.user_id,
                user_erp_code = erpCod,
                company = companyName,
                backcolor = model.backcolor,
                img_url = model.img_url,
                coupons_allotment_id = obj == null ? 0 : int.Parse(obj.ToString())
            };

            //优惠券记录表
            new coupons_used_logs(databaseprefix).Add(conn, trans, cul);

            if (obj == null)
            {
                return 0;
            }
            else
            {
                new DAL.manager_log(databaseprefix).Add(conn, trans, new Model.manager_log()
                {
                    user_id = manager.id,
                    action_type = _action_type,
                    add_time = DateTime.Now,
                    remark = remarks + " " + JsonConvert.SerializeObject(cul),
                    user_ip = ip,
                    user_name = manager.user_name

                });

                return Convert.ToInt32(obj);
            }
        }


        /// <summary>
        /// 系统优惠卷处理
        /// </summary>
        /// <returns></returns>
        public void CouponsRedo()
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                SqlTransaction trans = conn.BeginTransaction();

                try
                {
                    StringBuilder _sb = new StringBuilder();
                    _sb.Append("select ");
                    _sb.Append("id, coupon_id, user_id, amount, coupon_ids, contents, title, expiration_form, expiration_num, goods_category_ids, conditions_amount, superposition,");
                    _sb.Append("CONVERT(varchar(100), add_time, 120) add_time,");
                    _sb.Append("CONVERT(varchar(100), allotment_time, 120) allotment_time,");
                    _sb.Append("CONVERT(varchar(100), CONVERT(datetime, over_time), 120) over_time,");
                    _sb.Append("CONVERT(varchar(100), start_time, 120) start_time,");
                    _sb.Append("CONVERT(varchar(100), end_time, 120) end_time");

                    DataSet _ds_coupons_allotment = DbHelperSQL.Query(conn, trans, _sb.ToString() + " from dt_coupons_allotment;");
                    DataSet _ds_coupons_used_logs = DbHelperSQL.Query(conn, trans, _sb.ToString() + " from dt_coupons_used_logs;");

                    DataRow[] _drUsedLogs;

                    int index = 0;
                    foreach (DataRow dr in _ds_coupons_allotment.Tables[0].Rows)
                    {
                        if (index >= 10000)
                        {
                            index = 0;
                            trans.Commit();
                            trans = conn.BeginTransaction();
                        }

                        index++;

                        _drUsedLogs = _ds_coupons_used_logs.Tables[0].Select(
                            "user_id= " + dr["user_id"].ToString()
                            + " and coupon_id= " + dr["coupon_id"].ToString()
                            + " and title=  '" + dr["title"].ToString() + "'"
                            + " and contents= '" + dr["contents"].ToString() + "'"
                            + " and start_time= '" + dr["start_time"].ToString() + "'"
                            + " and end_time= '" + dr["end_time"].ToString() + "'"
                            + " and conditions_amount= " + dr["conditions_amount"].ToString()
                            + " and amount= " + dr["amount"].ToString()
                            + " and add_time= '" + dr["add_time"].ToString() + "'"
                            + " and expiration_form= " + dr["expiration_form"].ToString()
                            + " and expiration_num= " + dr["expiration_num"].ToString()
                            + " and superposition= " + dr["superposition"].ToString()
                            + " and goods_category_ids= '" + dr["goods_category_ids"].ToString() + "'"
                            + " and coupon_ids= '" + dr["coupon_ids"].ToString() + "'"
                            + " and allotment_time= '" + dr["allotment_time"].ToString() + "'"
                            + " and over_time= '" + dr["over_time"].ToString() + "'"
                            );

                        if (_drUsedLogs.Length > 0)
                        {
                            string ids = "";
                            foreach (DataRow _dr in _drUsedLogs)
                            {
                                ids += _dr["id"].ToString() + ",";
                            }
                            if (ids != "")
                            {
                                DbHelperSQL.ExecuteSql(conn, trans, "update dt_coupons_used_logs set coupons_allotment_id = " + dr["id"].ToString() + " where id in(" + ids.Substring(0, ids.Length - 1) + ")");
                            }
                        }
                    }

                    trans.Commit();

                }
                catch
                {
                    trans.Rollback();
                }
            }
        }

    }
}

